package com.sxfq.loansupermarket.backsystem.modular.arrival.service.impl;

import java.io.OutputStream;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.DateTimeKit;
import com.sxfq.loansupermarket.backsystem.core.util.CommUtils;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.modular.IntegralMall.entity.MembersArriveInfo;
import com.sxfq.loansupermarket.backsystem.modular.IntegralMall.entity.MembersUser;
import com.sxfq.loansupermarket.backsystem.modular.IntegralMall.entity.SearchMembersUser;
import com.sxfq.loansupermarket.backsystem.modular.arrival.dao.CustomerDao;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.FirstCustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.SearchCustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerPublicService;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.PayServicedInfo;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.RefereeShipInfo;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.VipLevelInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.ReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IReservationsService;
import com.sxfq.loansupermarket.backsystem.modular.weixin.config.WebChatConfig;
import com.sxfq.loansupermarket.backsystem.modular.weixin.entity.WxTokenInfo;
import com.sxfq.loansupermarket.backsystem.modular.weixin.service.IWxService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.ui.Model;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

@Service
@Transactional
public class CustomerServiceImpl implements ICustomerService {
    @Autowired
    private CustomerDao dao;

    @Autowired
    private SqlMapper sqlMapper;
    @Autowired
    private RedisTemplate redisTemplate;
    @Autowired
    IReservationsService reservationsService;

    @Autowired
    ICustomerPublicService customerPublicService;

    @Autowired
    private IWxService wxService;

    @Override
    public List<CustomerInfo> selectPageListByQueryGetMap(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        String table2 = tableHead + "customer_info";
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getSreach())) {
            params.put("sreach", searchInfo.getSreach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerName())) {
            params.put("customerName", searchInfo.getCustomerName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCondition())) {
            params.put("condition", "");
        } else {
            params.put("condition", "t.arrival_status!=8");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerPhone())) {
            params.put("customerPhone", searchInfo.getCustomerPhone());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDealStatus())) {
            if ("已结账".equals(searchInfo.getDealStatus())) {
                params.put("dealStatus", "t.deal_status ='已结账'");
            } else {
                params.put("dealStatus", "t.deal_status !='已结账'");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartArrivalTime())) {
            params.put("startArrivalTime", searchInfo.getStartArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndArrivalTime())) {
            params.put("endArrivalTime", searchInfo.getEndArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getReservations())) {
            params.put("reservations", searchInfo.getReservations());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartRevisitTime())) {
            params.put("startRevisitTime", searchInfo.getStartRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndRevisitTime())) {
            params.put("endRevisitTime", searchInfo.getEndRevisitTime());
        }

        if (StringUtil.isNotEmpty(searchInfo.getStartUpdateTime()) && StringUtil.isEmpty(searchInfo.getStartAddTime())) {
            params.put("startUpdateTime", searchInfo.getStartUpdateTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndUpdateTime())) {
            params.put("endUpdateTime", searchInfo.getEndUpdateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("startAddTime", searchInfo.getStartAddTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("gbtk", "and t.arrival_status!=1 and t.arrival_status!=9 group by t.id");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAddTime())) {
            params.put("endAddTime", searchInfo.getEndAddTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOppStatus())) {
            if ("1".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is not null");
            } else if ("2".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is null");
            } else {
                params.put("", "");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
            if (searchInfo.getOneChannel().indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = searchInfo.getOneChannel().split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneChannel", " t.one_channel in ('" + oneChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
            if (searchInfo.getTwoChannel().indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = searchInfo.getTwoChannel().split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoChannel", " t.two_channel in ('" + twoChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
            if (searchInfo.getThrChannel().indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = searchInfo.getThrChannel().split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrChannel", " t.thr_channel in ('" + thrChannel + "')");
            }
        }
        params.put("tableName", table2);
        params.put("tableHead", "tk.abbreviation ='" + tableHead + "'");
        params.put("table1", table1);
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryPage(page, params);
    }

    @Override
    public List<CustomerInfo> selectPageListByQueryGetMap2(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        String table2 = tableHead + "customer_info";
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getSreach())) {
            params.put("sreach", searchInfo.getSreach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerName())) {
            params.put("customerName", searchInfo.getCustomerName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCondition())) {
            params.put("condition", "");
        } else {
            params.put("condition", "t.arrival_status!=8");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerPhone())) {
            params.put("customerPhone", searchInfo.getCustomerPhone());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDealStatus())) {
            if ("已结账".equals(searchInfo.getDealStatus())) {
                params.put("dealStatus", "t.deal_status ='已结账'");
            } else {
                params.put("dealStatus", "t.deal_status !='已结账'");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartArrivalTime())) {
            params.put("startArrivalTime", searchInfo.getStartArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndArrivalTime())) {
            params.put("endArrivalTime", searchInfo.getEndArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getReservations())) {
            params.put("reservations", searchInfo.getReservations());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartRevisitTime())) {
            params.put("startRevisitTime", searchInfo.getStartRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndRevisitTime())) {
            params.put("endRevisitTime", searchInfo.getEndRevisitTime());
        }

        if (StringUtil.isNotEmpty(searchInfo.getStartUpdateTime()) && StringUtil.isEmpty(searchInfo.getStartAddTime())) {
            params.put("startUpdateTime", searchInfo.getStartUpdateTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndUpdateTime())) {
            params.put("endUpdateTime", searchInfo.getEndUpdateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("startAddTime", searchInfo.getStartAddTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("gbtk", "and t.arrival_status!=1 and t.arrival_status!=9 group by t.id");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAddTime())) {
            params.put("endAddTime", searchInfo.getEndAddTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOppStatus())) {
            if ("1".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is not null");
            } else if ("2".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is null");
            } else {
                params.put("", "");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
            if (searchInfo.getOneChannel().indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = searchInfo.getOneChannel().split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneChannel", " t.one_channel in ('" + oneChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
            if (searchInfo.getTwoChannel().indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = searchInfo.getTwoChannel().split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoChannel", " t.two_channel in ('" + twoChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
            if (searchInfo.getThrChannel().indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = searchInfo.getThrChannel().split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrChannel", " t.thr_channel in ('" + thrChannel + "')");
            }
        }
        params.put("tableName", table2);
        params.put("tableHead", "tk.abbreviation ='" + tableHead + "'");
        params.put("table1", table1);
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryPage2(page, params);
    }

    public int getQueryPagesCount(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        String table2 = tableHead + "customer_info";
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getSreach())) {
            params.put("sreach", searchInfo.getSreach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCondition())) {
            params.put("condition", "");
        } else {
            params.put("condition", "t.arrival_status!=8");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerName())) {
            params.put("customerName", searchInfo.getCustomerName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerPhone())) {
            params.put("customerPhone", searchInfo.getCustomerPhone());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDealStatus())) {
            if ("已结账".equals(searchInfo.getDealStatus())) {
                params.put("dealStatus", "t.deal_status ='已结账'");
            } else {
                params.put("dealStatus", "t.deal_status !='已结账'");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartArrivalTime())) {
            params.put("startArrivalTime", searchInfo.getStartArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndArrivalTime())) {
            params.put("endArrivalTime", searchInfo.getEndArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getReservations())) {
            params.put("reservations", searchInfo.getReservations());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartRevisitTime())) {
            params.put("startRevisitTime", searchInfo.getStartRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndRevisitTime())) {
            params.put("endRevisitTime", searchInfo.getEndRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartUpdateTime()) && StringUtil.isEmpty(searchInfo.getStartAddTime())) {
            params.put("startUpdateTime", searchInfo.getStartUpdateTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndUpdateTime())) {
            params.put("endUpdateTime", searchInfo.getEndUpdateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("startAddTime", searchInfo.getStartAddTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("gbtk", "and t.arrival_status!=1 and t.arrival_status!=9 group by t.id");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAddTime())) {
            params.put("endAddTime", searchInfo.getEndAddTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOppStatus())) {
            if ("1".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is not null");
            } else if ("2".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is null");
            } else {
                params.put("", "");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
            if (searchInfo.getOneChannel().indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = searchInfo.getOneChannel().split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneChannel", " t.one_channel in ('" + oneChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
            if (searchInfo.getTwoChannel().indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = searchInfo.getTwoChannel().split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoChannel", " t.two_channel in ('" + twoChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
            if (searchInfo.getThrChannel().indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = searchInfo.getThrChannel().split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrChannel", " t.thr_channel in ('" + thrChannel + "')");
            }
        }
        params.put("tableName", table2);
        params.put("tableHead", "tk.abbreviation ='" + tableHead + "'");
        params.put("table1", table1);
        return dao.getQueryPagesCount(page, params);
    }

    @Override
    public List<CustomerInfo> selectPageQfListByQueryGetMap(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        String table2 = tableHead + "customer_info";
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getSreach())) {
            params.put("sreach", searchInfo.getSreach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerName())) {
            params.put("customerName", searchInfo.getCustomerName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBirthday())) {
            params.put("birthday", "t.birthday like '%-"+searchInfo.getBirthday()+"'");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCondition())) {
            params.put("condition", "");
        } else {
            params.put("condition", "t.arrival_status!=8");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerPhone())) {
            params.put("customerPhone", searchInfo.getCustomerPhone());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDealStatus())) {
            if ("已结账".equals(searchInfo.getDealStatus())) {
                params.put("dealStatus", "t.deal_status ='已结账'");
            } else {
                params.put("dealStatus", "t.deal_status !='已结账'");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartArrivalTime())) {
            params.put("startArrivalTime", searchInfo.getStartArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndArrivalTime())) {
            params.put("endArrivalTime", searchInfo.getEndArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getReservations())) {
            params.put("reservations", searchInfo.getReservations());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartRevisitTime())) {
            params.put("startRevisitTime", searchInfo.getStartRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndRevisitTime())) {
            params.put("endRevisitTime", searchInfo.getEndRevisitTime());
        }

        if (StringUtil.isNotEmpty(searchInfo.getStartUpdateTime()) && StringUtil.isEmpty(searchInfo.getStartAddTime())) {
            params.put("startUpdateTime", searchInfo.getStartUpdateTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndUpdateTime())) {
            params.put("endUpdateTime", searchInfo.getEndUpdateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("startAddTime", searchInfo.getStartAddTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("gbtk", "and t.arrival_status!=1 and t.arrival_status!=9 group by t.customer_phone");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAddTime())) {
            params.put("endAddTime", searchInfo.getEndAddTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOppStatus())) {
            if ("1".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is not null");
            } else if ("2".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is null");
            } else {
                params.put("", "");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
            if (searchInfo.getOneChannel().indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = searchInfo.getOneChannel().split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneChannel", " t.one_channel in ('" + oneChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
            if (searchInfo.getTwoChannel().indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = searchInfo.getTwoChannel().split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoChannel", " t.two_channel in ('" + twoChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
            if (searchInfo.getThrChannel().indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = searchInfo.getThrChannel().split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrChannel", " t.thr_channel in ('" + thrChannel + "')");
            }
        }
        params.put("tableName", table2);
        params.put("tableHead", "tk.abbreviation ='" + tableHead + "'");
        params.put("table1", table1);
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryQfPage(page, params);
    }

    public int getQueryPagesQfCount(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        String table2 = tableHead + "customer_info";
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getSreach())) {
            params.put("sreach", searchInfo.getSreach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBirthday())) {
            params.put("birthday", "t.birthday like '%-"+searchInfo.getBirthday()+"'");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCondition())) {
            params.put("condition", "");
        } else {
            params.put("condition", "t.arrival_status!=8");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerName())) {
            params.put("customerName", searchInfo.getCustomerName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerPhone())) {
            params.put("customerPhone", searchInfo.getCustomerPhone());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDealStatus())) {
            if ("已结账".equals(searchInfo.getDealStatus())) {
                params.put("dealStatus", "t.deal_status ='已结账'");
            } else {
                params.put("dealStatus", "t.deal_status !='已结账'");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartArrivalTime())) {
            params.put("startArrivalTime", searchInfo.getStartArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndArrivalTime())) {
            params.put("endArrivalTime", searchInfo.getEndArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getReservations())) {
            params.put("reservations", searchInfo.getReservations());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartRevisitTime())) {
            params.put("startRevisitTime", searchInfo.getStartRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndRevisitTime())) {
            params.put("endRevisitTime", searchInfo.getEndRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartUpdateTime()) && StringUtil.isEmpty(searchInfo.getStartAddTime())) {
            params.put("startUpdateTime", searchInfo.getStartUpdateTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndUpdateTime())) {
            params.put("endUpdateTime", searchInfo.getEndUpdateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("startAddTime", searchInfo.getStartAddTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("gbtk", "and t.arrival_status!=1 and t.arrival_status!=9 group by t.customer_phone");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAddTime())) {
            params.put("endAddTime", searchInfo.getEndAddTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOppStatus())) {
            if ("1".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is not null");
            } else if ("2".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is null");
            } else {
                params.put("", "");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
            if (searchInfo.getOneChannel().indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = searchInfo.getOneChannel().split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneChannel", " t.one_channel in ('" + oneChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
            if (searchInfo.getTwoChannel().indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = searchInfo.getTwoChannel().split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoChannel", " t.two_channel in ('" + twoChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
            if (searchInfo.getThrChannel().indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = searchInfo.getThrChannel().split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrChannel", " t.thr_channel in ('" + thrChannel + "')");
            }
        }
        params.put("tableName", table2);
        params.put("tableHead", "tk.abbreviation ='" + tableHead + "'");
        params.put("table1", table1);
        return dao.getQueryPagesQfCount(page, params);
    }

    @Override
    public List<CustomerInfo> customerExport(SearchCustomerInfo searchInfo) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        String table1 = tableHead + "deal_info";
        Map<String, Object> params = new HashMap<String, Object>();
        if (StringUtil.isNotEmpty(searchInfo.getSreach())) {
            params.put("sreach", searchInfo.getSreach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerName())) {
            params.put("customerName", searchInfo.getCustomerName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCondition())) {
            params.put("condition", "");
        } else {
            params.put("condition", "t.arrival_status!=8");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerPhone())) {
            params.put("customerPhone", searchInfo.getCustomerPhone());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDealStatus())) {
            if ("已结账".equals(searchInfo.getDealStatus())) {
                params.put("dealStatus", "t.deal_status ='已结账'");
            } else {
                params.put("dealStatus", "t.deal_status !='已结账'");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartArrivalTime())) {
            params.put("startArrivalTime", searchInfo.getStartArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndArrivalTime())) {
            params.put("endArrivalTime", searchInfo.getEndArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getReservations())) {
            params.put("reservations", searchInfo.getReservations());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartRevisitTime())) {
            params.put("startRevisitTime", searchInfo.getStartRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndRevisitTime())) {
            params.put("endRevisitTime", searchInfo.getEndRevisitTime());
        }

        if (StringUtil.isNotEmpty(searchInfo.getStartUpdateTime()) && StringUtil.isEmpty(searchInfo.getStartAddTime())) {
            params.put("startUpdateTime", searchInfo.getStartUpdateTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndUpdateTime())) {
            params.put("endUpdateTime", searchInfo.getEndUpdateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("startAddTime", searchInfo.getStartAddTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("gbtk", "group by t.id");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAddTime())) {
            params.put("endAddTime", searchInfo.getEndAddTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
            if (searchInfo.getOneChannel().indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = searchInfo.getOneChannel().split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneChannel", " t.one_channel in ('" + oneChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
            if (searchInfo.getTwoChannel().indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = searchInfo.getTwoChannel().split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoChannel", " t.two_channel in ('" + twoChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
            if (searchInfo.getThrChannel().indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = searchInfo.getThrChannel().split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrChannel", " t.thr_channel in ('" + thrChannel + "')");
            }
        }
        params.put("tableName", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0));
        params.put("tableHead", "tk.abbreviation ='" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"" + "'");
        params.put("table1", table1);
        return dao.getQueryPagesExport(params);
    }

    public CustomerInfo selectOneById(String customerId) {
        String sql = "select " +
                " (CASE t2.customer_sex  WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END ) as sex ," +
                " (CASE t2.marital_status WHEN 1 THEN '已婚' WHEN 2 THEN '未婚' WHEN 3 THEN '离异' ELSE '未知' END ) as maritalStatusName ," +
                "t2.* from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " t2 where id= '" + customerId + "';";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return customerInfo;
    }

    //最近一次患者信息
    public CustomerInfo selectOneByReservationsId(String reservationsId) {
        String sql = "select " +
                " (CASE t2.customer_sex  WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END ) as sex ," +
                " (CASE t2.marital_status WHEN 1 THEN '已婚' WHEN 2 THEN '未婚' WHEN 3 THEN '离异' ELSE '未知' END ) as maritalStatusName," +
                " t2.* from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " t2 " +
                " where reservations_id= '" + reservationsId + "' order by arrival_time desc limit 0,1;";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return customerInfo;
    }

    //最近一次患者信息
    public CustomerInfo selectOneByReservationsId(String reservationsId, String customerTableName) {
        String sql = "select " +
                " (CASE t2.customer_sex  WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END ) as sex ," +
                " (CASE t2.marital_status WHEN 1 THEN '已婚' WHEN 2 THEN '未婚' WHEN 3 THEN '离异' ELSE '未知' END ) as maritalStatusName," +
                " t2.* from " + customerTableName + " t2 " +
                " where reservations_id= '" + reservationsId + "' order by arrival_time desc limit 0,1;";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return customerInfo;
    }

    //患者初次到店信息，如获得初诊tc
    public FirstCustomerInfo selectOneByReservationsIdFirst(String reservationsId, String customerTablesd) {
        String sql = "select id,appointment_pay_service,arrival_time,archiving,field_tc from (" + customerTablesd + ")  t order by t.arrival_time asc limit 1;";
        FirstCustomerInfo customerInfo = sqlMapper.selectOne(sql, FirstCustomerInfo.class);
        return customerInfo;
    }

    //患者初次到店信息，如获得初诊tc
    public CustomerInfo selectOneByReservationsIdFirsts(String reservationsId, String customerTablesd) {
        String sql = "select * from (" + customerTablesd + ")  t order by t.arrival_time asc limit 1;";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return customerInfo;
    }

    //患者最近到店信息
    public CustomerInfo selectOneByReservationsIdLast(String reservationsId) {
        String sql = "select id,customer_status from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " " +
                " where reservations_id= '" + reservationsId + "' and arrival_time is not null and customer_status in ('新客首次','新客多次','老客','复诊') " +
                " and arrival_status!=1 and arrival_status!=6 and arrival_status!=7 and arrival_status!=8 and arrival_status!=9 and arrival_status!=10 order by arrival_time desc limit 1;";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return customerInfo;
    }

    public MembersArriveInfo memberDistinguishInUp(String reservationsId) {
        String sssql = "select quality from member_distinguish_info where reservations_id='" + reservationsId + "'";
        MembersArriveInfo membersArriveInfo = sqlMapper.selectOne(sssql, MembersArriveInfo.class);
        String sssql1 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where reservations_id='" + reservationsId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sssql1, CustomerInfo.class);
        String membersImageBs = ShiroKit.getSessionAttr("membersImageBs");
        Base64.Decoder decoder = Base64.getDecoder();
        if (membersImageBs != null) {
            byte[] b = decoder.decode(membersImageBs);
            // 处理数据
            for (int i = 0; i < b.length; ++i) {
                if (b[i] < 0) {
                    b[i] += 256;
                }
            }
            try {
                String iconName = ShiroKit.getSessionAttr("membersId") + ".jpg";
                String httpUrl = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\memberTouxiang\\" + iconName + "";
                OutputStream out = new FileOutputStream(httpUrl);
                out.write(b);
                out.flush();
                out.close();
                String avatarUrl = "http://www.yiyatest.cn:8068/memberTouxiang/" + iconName + "";
                /* 找出指定的2个字符在 该字符串里面的 位置 */
                if (membersArriveInfo == null) {
                    if (ShiroKit.getSessionAttr("membersFeature") != null) {
                        String sql = "INSERT INTO `member_distinguish_info` (`reservations_id`, `medical_record_number`, `member_name`, `member_phone`, " +
                                "`member_age`, `member_sex`, `avatar`, `feature`, `quality`,member_id) VALUES " +
                                "('" + customerInfo.getReservationsId() + "','" + customerInfo.getMedicalRecordNumber() + "', '" + customerInfo.getCustomerName() + "'," +
                                " '" + customerInfo.getCustomerPhone() + "', " + customerInfo.getCustomerAge() + ", " + customerInfo.getCustomerSex() + ", " +
                                " '" + avatarUrl + "', '" + ShiroKit.getSessionAttr("membersFeature") + "'," +
                                " '" + ShiroKit.getSessionAttr("membersQuality") + "', '" + ShiroKit.getSessionAttr("membersId") + "');";
                        sqlMapper.insert(sql);
                    }
                } else {
                    if (ShiroKit.getSessionAttr("quality") != null) {
                        if (membersArriveInfo.getQuality() < Integer.parseInt(ShiroKit.getSessionAttr("quality"))) {
                            String usql = "update `member_distinguish_info` set quality='" + ShiroKit.getSessionAttr("quality") + "' where reservations_id='" + reservationsId + "'";
                            sqlMapper.update(usql);
                        }
                    }
                }
                ShiroKit.setSessionAttr("membersFeature", null);
                ShiroKit.setSessionAttr("membersImageBs", null);
                ShiroKit.setSessionAttr("membersQuality", null);
                ShiroKit.setSessionAttr("membersId", null);
            } catch (IOException e) {
                e.printStackTrace();
                String sssql2 = "select * from member_distinguish_info where reservations_id='" + reservationsId + "'";
                MembersArriveInfo membersArriveInfo2 = sqlMapper.selectOne(sssql2, MembersArriveInfo.class);
                membersArriveInfo2.setStatus(1);
                return membersArriveInfo2;
            } finally {
                String sssql2 = "select * from member_distinguish_info where reservations_id='" + reservationsId + "'";
                MembersArriveInfo membersArriveInfo2 = sqlMapper.selectOne(sssql2, MembersArriveInfo.class);
                membersArriveInfo2.setStatus(1);
                return membersArriveInfo2;
            }
        } else {
            MembersArriveInfo membersArriveInfo2 = new MembersArriveInfo();
            membersArriveInfo2.setStatus(2);
            return membersArriveInfo2;
        }
    }

    @Override
    public int updateCustomerArrivalStatus(int arrivalStatus, String customerId) {
        String sql = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " set arrival_status = " + arrivalStatus + " where id = '" + customerId + "'";
        return sqlMapper.update(sql);
    }

    //设置开始时间
    public String getStartTime() {
        String starttime = DateTimeKit.now();
        String time = starttime.substring(0, 11);
        int hour = Integer.parseInt(starttime.substring(11, 13));
        int minute = Integer.parseInt(starttime.substring(14, 16));
        minute = (minute / 15) * 15;
        String minutes = minute + "";
        if ("0".equals(minutes)) {
            minutes = "00";
        }
        starttime = time + hour + ":" + minutes + ":00";
        return starttime;
    }

    //设置结束时间
    public String getEndTime() {
        String starttime = DateTimeKit.now();
        String time = starttime.substring(0, 11);
        int hour = Integer.parseInt(starttime.substring(11, 13));
        int minute = Integer.parseInt(starttime.substring(14, 16));
        minute = (minute / 15) * 15;
        String minutes = minute + "";
        if ("0".equals(minutes)) {
            minutes = "00";
        }
        String endtime = "";
        if (hour + 1 == 24) {
            time = time.substring(0, 9) + (Integer.parseInt(time.substring(9, 10)) + 1) + " ";
            endtime = time + "00" + ":" + minutes + ":00";
        } else {
            endtime = time + (hour + 1) + ":" + minutes + ":00";
        }
        return endtime;
    }

    //重咨患者到店
    public int insertReturncz(CustomerInfo customerInfo, String id) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        String tableName = tableHead + "customer_info";
        Double moneyBalance = 0.00;
        Double jfbalance = 0.00;
        if (StringUtil.isNotEmpty(customerInfo.getAbbreviation())) {
            tableName = customerInfo.getAbbreviation() + "customer_info";
        }
        String getMoneyBalance = "select sum(a.actual_account_balance) as moneyBalance from reservemoney_deal a " +
                " where a.reservations_id='" + customerInfo.getReservationsId() + "' " +
                " and a.add_store='" + ShiroKit.getUser().getOutpatientName() + "'";
        CustomerInfo customerInfo1 = sqlMapper.selectOne(getMoneyBalance, CustomerInfo.class);
        if (customerInfo1 != null) {
            moneyBalance = customerInfo1.getMoneyBalance();
        }
        String getjFBalance = "select sum(a.score_balance) as jfbalance from score_deal a " +
                " where a.reservations_id='" + customerInfo.getReservationsId() + "' ";
        CustomerInfo customerInfo2 = sqlMapper.selectOne(getjFBalance, CustomerInfo.class);
        if (customerInfo2 != null) {
            jfbalance = customerInfo2.getJfbalance();
        }
        String referralerPerson = "";
        String referralerPersoned = "";
        String referralerTime = "";
        String referralerRemarks = "";
        String marketFilingTime = "";
        if (customerInfo.getMarketFilingTime() == null || customerInfo.getMarketFilingTime() == "") {
            marketFilingTime = "null";
        } else {
            marketFilingTime = "'" + customerInfo.getMarketFilingTime() + "'";
        }
        if (StringUtil.isNotEmpty(customerInfo.getReferralerPerson())) {
            referralerPerson = "'" + customerInfo.getReferralerPerson() + "'";
        } else {
            referralerPerson = "null";
        }
        if (StringUtil.isNotEmpty(customerInfo.getReferralerPersoned())) {
            referralerPersoned = "'" + customerInfo.getReferralerPersoned() + "'";
        } else {
            referralerPersoned = "null";
        }
        if (StringUtil.isNotEmpty(customerInfo.getReferralerTime())) {
            referralerTime = "'" + customerInfo.getReferralerTime() + "'";
        } else {
            referralerTime = null;
        }
        if (StringUtil.isNotEmpty(customerInfo.getReferralerRemarks())) {
            referralerRemarks = "'" + customerInfo.getReferralerRemarks() + "'";
        } else {
            referralerRemarks = "null";
        }
        String sql = "INSERT INTO `" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + "` (`id`,`first_visit_date`," +
                "`age_range`,`reservations_id`,`appointment_pay_service`,`arrival_store`,`phone_shar_ship`,`deal_status`, `customer_name`, " +
                "`pseudonym`, `customer_phone`,customer_phone2, " +
                "`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                "`field_tc`,`query_tc`, `customer_sex`, customer_sex_head,`customer_age`, `birthday`," +
                " `address`, `referee`, `referee_ship`, `id_card_no`," +
                " `fixed_telephone`, `is_have_children`, `email`, `zip_code`, " +
                "`customer_status`, `number_of_audits`, `auditor_time`, `final_auditor`," +
                "  `non_transaction_level`, `transaction_level`, `characters`, " +
                "`consumption_habits`, `is_return_visit`, `cause_of_no_return_visit`, `consum_ability`," +
                " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                " `province`, `cities`, `region`, `medical_record_number`, " +
                "`actual_account_balance`,`give_account_balance` ,`account_arrears`,`receivables`,`cash_receipts`,`yhbalance`,`jfbalance`,`money_balance`," +
                " `marital_status`, `occupation`, `archiving`, `entry_clerk`,`update_people`,`update_time`," +
                " `filing_time`, `arrival_time`, `remarks`, `status`, `diagnosis_status`,`arrival_status`,`doctor`,`start_time`,`end_time`," +
                "`create_appointment_time`,yellow_number,xcustomer_status,scarchiving,treatment_unit,szm,referraler_person,referraler_personed,referraler_time,referraler_remarks,market_filing_time) " +
                " select '" + customerInfo.getId() + "',first_visit_date," +
                "(case " +
                " WHEN (0<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<10) then '0-9' " +
                " WHEN (10<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<20) then '10-19' " +
                " WHEN (20<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<30) then '20-29' " +
                " WHEN (30<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<40) then '30-39' " +
                " WHEN (40<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<50) then '40-49' " +
                " WHEN (50<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<60) then '50-59' " +
                " WHEN (60<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<70) then '60-69' " +
                " WHEN (70<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<80) then '70-79' " +
                " WHEN (80<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<90) then '80-89' " +
                " WHEN (90<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<100) then '90-99' " +
                " else '99+' end)," +
                "reservations_id," +
                " '','" + ShiroKit.getUser().getOutpatientName() + "',phone_shar_ship,'未开单','" + customerInfo.getCustomerName() + "'," +
                "pseudonym,customer_phone,customer_phone2,`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                "'" + customerInfo.getFieldTc() + "','" + customerInfo.getFieldTc() + "', `customer_sex`, customer_sex_head,(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')), `birthday`," +
                " `address`, `referee`, `referee_ship`, `id_card_no`," +
                " `fixed_telephone`, `is_have_children`, `email`, `zip_code`, " +
                " '" + customerInfo.getCustomerStatus() + "', `number_of_audits`, `auditor_time`, `final_auditor`," +
                "  `non_transaction_level`, `transaction_level`, `characters`, " +
                "`consumption_habits`, `is_return_visit`, `cause_of_no_return_visit`, `consum_ability`," +
                " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                " `province`, `cities`, `region`, '" + customerInfo.getMedicalRecordNumber() + "', " +
                "`actual_account_balance`,`give_account_balance` ,`account_arrears`,`receivables`,`cash_receipts`,0," + jfbalance + "," + moneyBalance + "," +
                " `marital_status`, `occupation`,'" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "'," +
                " `filing_time`, '" + DateTimeKit.now() + "','" + customerInfo.getRemarks() + "', `status`, '未看诊',2,'" + customerInfo.getDoctor() + "'," +
                "'" + customerInfo.getStartTime() + "','" + customerInfo.getEndTime() + "'," +
                "'" + DateTimeKit.now() + "','" + customerInfo.getYellowNumber() + "','复到','" + customerInfo.getScarchiving() + "','" + customerInfo.getTreatmentUnit() + "',firstconcats(customer_name) ," + referralerPerson + "," + referralerPersoned + "," + referralerTime + "," + referralerRemarks + "," + marketFilingTime + " " +
                "from " + tableName + " where id ='" + customerInfo.getCustomerId() + "' ;";
        return sqlMapper.insert(sql);
    }

    //市场人员预约复诊洁牙
    public int insertReturnfzjy(String customid, String projectData, String treatmentUnit, String customerPhone, String fieldTc, String tableName, String customerId,
                                String startTimeValue, String endTimeValue, String doctor, String remarks,String isMarketTc) {
        String sql = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " (`id`,scarchiving,`diagnosis_status`,`first_visit_date`,`actual_account_balance`," +
                "`give_account_balance`,`account_arrears`,`age_range`,`reservations_id`,`appointment_pay_service`," +
                "`arrival_store`,`phone_shar_ship`,`deal_status`, `status`,`customer_name`, `pseudonym`,szm, " +
                "`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                " `customer_sex`,customer_sex_head, `customer_age`, `birthday`,  `address`, `referee`, `referee_ship`, `id_card_no`," +
                " `fixed_telephone`, `is_have_children`, `email`, `zip_code`,treatment_unit, " +
                "  `number_of_audits`, `auditor_time`, `final_auditor`,`yhbalance`,`jfbalance`,`money_balance`," +
                "  `non_transaction_level`, `transaction_level`, `characters`, " +
                "`consumption_habits`, `is_return_visit`, `cause_of_no_return_visit`, `consum_ability`," +
                " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                " `province`, `cities`, `region`, `medical_record_number`, " +
                " `marital_status`, `occupation`, `archiving`, `entry_clerk` ,`update_people`," +
                "  `filing_time`," +
                " `customer_phone`,customer_phone2,`field_tc`,`query_tc`,`doctor`,`remarks`,`remarks_reservation`,`start_time`,`end_time`,create_appointment_time,yellow_number,isMarketTc)  " +
                " select '" + customid + "',scarchiving,'未看诊',first_visit_date,actual_account_balance," +
                " give_account_balance,account_arrears, " +
                "(case " +
                " WHEN (0<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<10) then '0-9' " +
                " WHEN (10<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<20) then '10-19' " +
                " WHEN (20<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<30) then '20-29' " +
                " WHEN (30<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<40) then '30-39' " +
                " WHEN (40<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<50) then '40-49' " +
                " WHEN (50<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<60) then '50-59' " +
                " WHEN (60<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<70) then '60-69' " +
                " WHEN (70<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<80) then '70-79' " +
                " WHEN (80<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<90) then '80-89' " +
                " WHEN (90<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<100) then '90-99' " +
                " else '99+' end)," +
                " reservations_id,'" + projectData + "'," +
                " '" + ShiroKit.getUser().getOutpatientName() + "',phone_shar_ship,'未开单','预约',customer_name , pseudonym, szm, " +
                "one_medium,two_medium,thr_medium, one_channel,two_channel,thr_channel," +
                " customer_sex,customer_sex_head, (DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')), birthday, address, referee, referee_ship, id_card_no," +
                " fixed_telephone, is_have_children, email, zip_code,'" + treatmentUnit + "'," +
                "   number_of_audits, auditor_time, final_auditor,0,jfbalance,money_balance," +
                " non_transaction_level,transaction_level,characters," +
                " consumption_habits,is_return_visit,cause_of_no_return_visit,consum_ability," +
                " qq_number,wechat_number,other_contact,country," +
                " province, cities,region,medical_record_number,marital_status," +
                " occupation,'" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "', '" + DateTimeKit.now() + "'," +
                " '" + customerPhone + "',customer_phone2,'" + fieldTc + "','" + fieldTc + "','" + doctor + "'," +
                "remarks,'" + remarks + "','" + startTimeValue + "','" + endTimeValue + "','" + DateTimeKit.now() + "',yellow_number,'"+isMarketTc+"' " +
                " from " + tableName + " where id='" + customerId + "' ";
        //在另一家创建了新预约
        String sql2 = "insert into distinction_customer_status (reservations_id,customer_name,customer_phone,arrival_store,abbreviation,deal_status,add_time,update_time,medical_record_number,status) " +
                " select reservations_id,customer_name,'" + customerPhone + "','" + ShiroKit.getUser().getOutpatientName()
                + "','" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"" + "','未成交','" + DateTimeKit.now() + "','" + DateTimeKit.now() + "',medical_record_number,2 " +
                " from " + tableName + " where id='" + customerId + "' ";
        sqlMapper.insert(sql2);
        return sqlMapper.insert(sql);
    }

    public String getId() {
        String id = CommUtils.getUUID();
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        id = id + tableHead;
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        id = id + randomNumber;
        return id;
    }

    public int addCustomerErnest(String remarks, String customerId, String[] str, String mode, String reservationsId, String fieldTcValue, Model model) {
        Double money = 0.0;
        String checkoutMethod = "";
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        String tableNamer = tableHead + "recharge_info";
        String sql1 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where id='" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        if ("转账".equals(mode)) {
            String sql2 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where arrival_status!=1 and arrival_status!=6 and arrival_status!=7 and arrival_status!=8 and arrival_status!=9 and arrival_status!=10 and arrival_time is not null " +
                    " and reservations_id='" + reservationsId + "' limit 0,1 ";
            CustomerInfo customerInfor2 = sqlMapper.selectOne(sql2, CustomerInfo.class);
            String customerIdr = customerInfor2.getId();
            for (int i = 0; i < str.length; i++) {//收费方式
                checkoutMethod = str[i].split("#")[0];
                money = Double.parseDouble(str[i].split("#")[1]);
                Random rand = new Random();
                int randomNumber = rand.nextInt(999999);
                String id = randomNumber + DateTimeKit.now();
                //扣除本人的储值 //插入结账表
                String sql2c = " INSERT INTO `" + tableNamer + "` (`id`, `status`, `customer_id`, `reservations_id`, " +
                        "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                        "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                        "  `remarks`, `is_record`,tc_name,isEarnes,hospital_performance,section_performance) values  " +
                        " ('" + id + "',1,'" + customerId + "','" + customerInfo.getReservationsId() + "', " +
                        " '" + ShiroKit.getUser().getAccount() +
                        "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                        + checkoutMethod + "' , '-" + money + "', '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "', " +
                        " '储值转账" + remarks + "','是','" + fieldTcValue + "',2, '-" + money + "',0) ";
                sqlMapper.insert(sql2c);
                //插入储值收费记录表
                String sql2c1 = " INSERT INTO reserveMoney_deal (`status`, `reservations_id`, `customer_id`,`reservations_ids`, " +
                        "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                        "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                        "  `remarks`, `is_record`,tc_name,add_store,mode,deal_id) values  " +
                        " (1,'" + customerInfo.getReservationsId() + "', '" + customerId + "', '" + reservationsId + "'," +
                        " '" + ShiroKit.getUser().getAccount() +
                        "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                        + checkoutMethod + "' , '-" + money + "', '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "', " +
                        " '" + remarks + "','是','" + fieldTcValue + "','" + ShiroKit.getUser().getOutpatientName() + "'" +
                        ",'" + mode + "','" + id + "') ";
                sqlMapper.insert(sql2c1);
                //转入收款人的账户
                String sql22 = " INSERT INTO reserveMoney_deal ( `status`,`reservations_id`,`customer_id`, `reservations_idz`," +
                        "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                        "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                        "  `remarks`, `is_record`,tc_name,add_store,mode,deal_id) values  " +
                        " (1,'" + reservationsId + "','" + customerInfor2.getId() + "','" + customerInfo.getReservationsId() + "'," +
                        " '" + ShiroKit.getUser().getAccount() +
                        "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                        + checkoutMethod + "' , '" + money + "', '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "', " +
                        " '" + remarks + "','是','" + fieldTcValue + "','" + ShiroKit.getUser().getOutpatientName() + "'" +
                        ",'收账','zz" + id + "') ";
                sqlMapper.insert(sql22);
                String sql22r = " INSERT INTO " + tableNamer + " (`id`, `status`, `customer_id`, `reservations_id`, " +
                        "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                        "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                        "  `remarks`, `is_record`,tc_name,isEarnes,hospital_performance,section_performance) values  " +
                        " ('zz" + id + "',1,'" + customerIdr + "','" + reservationsId + "', " +
                        " '" + ShiroKit.getUser().getAccount() +
                        "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                        + checkoutMethod + "' , '" + money + "', '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "', " +
                        " '储值转账" + remarks + "','是','" + fieldTcValue + "',2, '" + money + "',0) ";
                sqlMapper.insert(sql22r);
                String sql11 = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " a set money_balance=money_balance+'" + money + "',update_people='" + ShiroKit.getUser().getAccount() + "'," +
                        "update_time='" + DateTimeKit.now() + "' where reservations_id='" + reservationsId + "'";
                sqlMapper.update(sql11);
                String sql = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " a set money_balance=money_balance+'-" + money + "',update_people='" + ShiroKit.getUser().getAccount() + "'," +
                        "update_time='" + DateTimeKit.now() + "' where reservations_id='" + customerInfo.getReservationsId() + "'";
                String sqlright = "update " + tableNamer + " a set a.recharges_time=left( DATE_ADD(a.recharge_time,INTERVAL 1 DAY) ,10) " +
                        " where right(a.recharge_time,8)>= '" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"SttleAccountsTime",0) + "' and a.recharges_time is null;";
                sqlMapper.update(sqlright);
                String qlleft = "update " + tableNamer + " a set a.recharges_time=left(a.recharge_time,10) " +
                        " where right(a.recharge_time,8)<'" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"SttleAccountsTime",0) + "' and a.recharges_time is null;";
                sqlMapper.update(qlleft);
                sqlMapper.update(sql);
            }
        } else {
            String remark = "";
            if ("退款".equals(mode)) {
                remark = "储值退款";
            } else if ("充值".equals(mode)) {
                remark = "储值充值";
            }
            Double moneysum = 0.0;
            for (int i = 0; i < str.length; i++) {//收费方式
                checkoutMethod = str[i].split("#")[0];
                money = Double.parseDouble(str[i].split("#")[1]);
                Random rand = new Random();
                int randomNumber = rand.nextInt(999999);
                String id = randomNumber + DateTimeKit.now();
                moneysum += money;
                String sql2 = " INSERT INTO `" + tableNamer + "` (`id`, `status`, `customer_id`, `reservations_id`, " +
                        "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                        "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                        "  `remarks`, `is_record`,tc_name,isEarnes,hospital_performance,section_performance) values  " +
                        " ('" + id + "',1,'" + customerId + "','" + customerInfo.getReservationsId() + "', " +
                        " '" + ShiroKit.getUser().getAccount() +
                        "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                        + checkoutMethod + "' , " + money + ", '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "', " +
                        " '" + remark + remarks + "','是','" + fieldTcValue + "',2 , " + money + ",0) ";
                sqlMapper.insert(sql2);
                String sql2r = " INSERT INTO reserveMoney_deal (`status`, `reservations_id`, `customer_id`," +
                        "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                        "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                        "  `remarks`, `is_record`,tc_name,add_store,mode,deal_id) values  " +
                        " (1,'" + customerInfo.getReservationsId() + "', '" + customerId + "', " +
                        " '" + ShiroKit.getUser().getAccount() +
                        "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                        + checkoutMethod + "' , " + money + ", '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "', " +
                        " '" + remarks + "','是','" + fieldTcValue + "','" + ShiroKit.getUser().getOutpatientName() + "'" +
                        ",'" + mode + "','" + id + "') ";
                sqlMapper.insert(sql2r);
                String sql = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " a set money_balance=money_balance+'" + money + "',update_people='" + ShiroKit.getUser().getAccount() + "'," +
                        "update_time='" + DateTimeKit.now() + "' where reservations_id='" + customerInfo.getReservationsId() + "'";
                sqlMapper.update(sql);
                String sqlright = "update " + tableNamer + " a set a.recharges_time=left( DATE_ADD(a.recharge_time,INTERVAL 1 DAY) ,10) " +
                        " where right(a.recharge_time,8)>= '" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"SttleAccountsTime",0) + "' and a.recharges_time is null;";
                sqlMapper.update(sqlright);
                String qlleft = "update " + tableNamer + " a set a.recharges_time=left(a.recharge_time,10) " +
                        " where right(a.recharge_time,8)<'" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"SttleAccountsTime",0) + "' and a.recharges_time is null;";
                sqlMapper.update(qlleft);
            }
            if ("1".equals(Integer.parseInt(redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"MemberSwitch",0)+""))) {
                //返现
                if ("退款".equals(mode)) {
                    moneysum = -moneysum;
                }
                String sqlb = "select * from "+ redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"vip_level_info",0)+";";
                List<VipLevelInfo> infod2 = sqlMapper.selectList(sqlb, VipLevelInfo.class);
                Double allReturnCash = 0.0;
                for (VipLevelInfo vinfo : infod2) {
                    if (vinfo.getMoneyCoefficientTwo() > moneysum && moneysum >= vinfo.getMoneyCoefficient()) {
                        allReturnCash = vinfo.getAddMoney();
                    }
                }
                if ("退款".equals(mode)) {
                    allReturnCash = -allReturnCash;
                }
                String insert2 = "insert into return_cash_info (reservations_id,customer_name,customer_phone,score_balance,mode,remarks,add_people,add_time,add_store) values" +
                        " ('" + customerInfo.getReservationsId() + "','" + customerInfo.getCustomerName() + "','" + customerInfo.getCustomerPhone() + "'," +
                        " '" + allReturnCash + "','返现','返现" + remarks + "'," +
                        " '" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "','" + ShiroKit.getUser().getOutpatientName() + "')";
                sqlMapper.insert(insert2);
            }
        }
        return 1;
    }

    public int addOrthodonticsDeal(String remarks, String customerId, String[] str, String mode, String fieldTcValue, Model model) {
        Double money = 0.0;
        String checkoutMethod = "";
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        String tableNamer = tableHead + "recharge_info";
        String sql1 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where id='" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        String sql="select ifnull(sum(actual_account_balance),0) as moneyBalance from reservemoney_orthodontics_deal t1" +
                " where reservations_id='"+customerInfo.getReservationsId()+"' and (now() between t1.recharge_time and t1.end_time) ";
        CustomerInfo cu=sqlMapper.selectOne(sql,CustomerInfo.class);
        String remark = "";
        if ("退款".equals(mode)) {
            remark = "种植牙俱乐部退回费";
        } else if ("充值".equals(mode)) {
            if(cu.getMoneyBalance()==1000){
                return 2;
            }
            String sql2 = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + "" +
                    " set deal_status='已结账',isdeal_status='已成交'" +
                    " where id='" + customerId + "' ";
            sqlMapper.update(sql2);
            String sqldist = "select a.id from distinction_customer_status a where a.reservations_id='" + customerInfo.getReservationsId() + "' and deal_status='未成交'";
            List<PayServicedInfo> sqldistl = sqlMapper.selectList(sqldist, PayServicedInfo.class);
            if(sqldistl.size()>0){  //之前未成交，本次成交了，修改时间状态
                String up1 = "update distinction_customer_status set deal_status='已成交',update_time='" + DateTimeKit.now() + "' where reservations_id='" + customerInfo.getReservationsId() + "' " +
                        " and status=1  and arrival_store='" + ShiroKit.getUser().getOutpatientName() + "';";
                sqlMapper.update(up1);
            }
            remark = "种植牙俱乐部入会充值";
        }
        Double moneysum = 0.0;
        for (int i = 0; i < str.length; i++) {//收费方式
            checkoutMethod = str[i].split("#")[0];
            money = Double.parseDouble(str[i].split("#")[1]);
            Random rand = new Random();
            int randomNumber = rand.nextInt(999999);
            String id = randomNumber + DateTimeKit.now();
            moneysum += money;
            String sql2 = " INSERT INTO `" + tableNamer + "` (`id`, `status`, `customer_id`, `reservations_id`, " +
                    "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                    "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                    "  `remarks`, `is_record`,tc_name,isEarnes,hospital_performance,section_performance) values  " +
                    " ('" + id + "',1,'" + customerId + "','" + customerInfo.getReservationsId() + "', " +
                    " '" + ShiroKit.getUser().getAccount() +
                    "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                    + checkoutMethod + "' , " + money + ", '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "', " +
                    " '" + remark + remarks + "','是','" + fieldTcValue + "',10 , " + money + ",0) ";
            sqlMapper.insert(sql2);
            String sql2r = " INSERT INTO reservemoney_orthodontics_deal (`status`, `reservations_id`, `customer_id`," +
                    "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                    "  `actual_account_balance`, `recharge_time`, `operation_time`,end_time, " +
                    "  `remarks`, `is_record`,tc_name,add_store,mode,deal_id) values  " +
                    " (1,'" + customerInfo.getReservationsId() + "', '" + customerId + "', " +
                    " '" + ShiroKit.getUser().getAccount() +
                    "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                    + checkoutMethod + "' , " + money + ", '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "',DATE_ADD('" + DateTimeKit.now() + "', INTERVAL 1 YEAR),  " +
                    " '" + remarks + "','是','" + fieldTcValue + "','" + ShiroKit.getUser().getOutpatientName() + "'" +
                    ",'" + mode + "','" + id + "') ";
            sqlMapper.insert(sql2r);
            String sqlright = "update " + tableNamer + " a set a.recharges_time=left( DATE_ADD(a.recharge_time,INTERVAL 1 DAY) ,10) " +
                    " where right(a.recharge_time,8)>= '" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"SttleAccountsTime",0) + "' and a.recharges_time is null;";
            sqlMapper.update(sqlright);
            String qlleft = "update " + tableNamer + " a set a.recharges_time=left(a.recharge_time,10) " +
                    " where right(a.recharge_time,8)<'" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"SttleAccountsTime",0) + "' and a.recharges_time is null;";
            sqlMapper.update(qlleft);
        }
        return 1;
    }

    @Override
    public List<CustomerInfo> selectPageListById(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        //查询条件 时间和门店
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            params.put("endTime", searchInfo.getEndTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore())) {
            params.put("arrivalStore", searchInfo.getArrivalStore());
        }
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getIdListQueryPage(page, params);
    }

    @Override
    public int getQueryPagesByIdCount(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        //查询条件 时间和门店
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            params.put("endTime", searchInfo.getEndTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore())) {
            params.put("arrivalStore", searchInfo.getArrivalStore());
        }
        return dao.getQueryPageCountId(page, params);
    }

    @Override
    public List<MembersUser> selectCustomerMemberList(Page<MembersUser> page, SearchMembersUser searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        //查询条件 时间和门店
        if (StringUtil.isNotEmpty(searchInfo.getStartCTime())) {
            params.put("startCTime", searchInfo.getStartCTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndCTime())) {
            params.put("endCTime", searchInfo.getEndCTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartUpdateTime())) {
            params.put("startUpdateTime", searchInfo.getStartUpdateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndUpdateTime())) {
            params.put("endUpdateTime", searchInfo.getEndUpdateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getPhone())) {
            params.put("phone", searchInfo.getPhone());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOutpatientName()) && !"请选择".equals(searchInfo.getOutpatientName())) {
            String outpatientName = "";
            String outpatientName2[] = searchInfo.getOutpatientName().split(",");
            if (outpatientName2.length == 1) {
                outpatientName = outpatientName2[0];
            } else {
                for (int i = 0; i < outpatientName2.length; i++) {
                    if (i == (outpatientName2.length - 1)) {
                        outpatientName += "'" + outpatientName2[i];
                    } else if (i == 0) {
                        outpatientName += outpatientName2[i] + "'" + ",";
                    } else {
                        outpatientName += "'" + outpatientName2[i] + "'" + ",";
                    }
                }
            }
            params.put("outpatientName", " d.simplename in ('" + outpatientName + "')");
        }
        params.put("tableName", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0));
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getcustomerMemberQueryPage(page, params);
    }

    @Override
    public int getQueryPagesMemberListCount(Page<MembersUser> page, SearchMembersUser searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        //查询条件 时间和门店
        if (StringUtil.isNotEmpty(searchInfo.getStartCTime())) {
            params.put("startCTime", searchInfo.getStartCTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndCTime())) {
            params.put("endCTime", searchInfo.getEndCTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartUpdateTime())) {
            params.put("startUpdateTime", searchInfo.getStartUpdateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndUpdateTime())) {
            params.put("endUpdateTime", searchInfo.getEndUpdateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getPhone())) {
            params.put("phone", searchInfo.getPhone());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOutpatientName()) && !"请选择".equals(searchInfo.getOutpatientName())) {
            String outpatientName = "";
            String outpatientName2[] = searchInfo.getOutpatientName().split(",");
            if (outpatientName2.length == 1) {
                outpatientName = outpatientName2[0];
            } else {
                for (int i = 0; i < outpatientName2.length; i++) {
                    if (i == (outpatientName2.length - 1)) {
                        outpatientName += "'" + outpatientName2[i];
                    } else if (i == 0) {
                        outpatientName += outpatientName2[i] + "'" + ",";
                    } else {
                        outpatientName += "'" + outpatientName2[i] + "'" + ",";
                    }
                }
            }
            params.put("outpatientName", " d.simplename in ('" + outpatientName + "')");
        }
        params.put("tableName", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0));
        return dao.getcustomerMemberQueryPageCount(page, params);
    }

    //匹配到店
    public int insert(CustomerInfo info) {
        String filingTime = "";
        String birthday = "";
        String auditorTime = "";
        String customerPhone2 = "";
        String phoneSharShip = "";
        String marketFilingTime = "";
        if (info.getFilingTime() == null || info.getFilingTime() == "") {
            filingTime = DateTimeKit.now();
        } else {
            filingTime = "'" + info.getFilingTime() + "'";
        }
        if (info.getBirthday() == null || info.getBirthday() == "") {
            birthday = "null";
        } else {
            birthday = "'" + info.getBirthday() + "'";
        }
        if (info.getAuditorTime() == null || info.getAuditorTime() == "") {
            auditorTime = "null";
        } else {
            auditorTime = "'" + info.getAuditorTime() + "'";
        }
        if (info.getMarketFilingTime() == null || info.getMarketFilingTime() == "") {
            marketFilingTime = "null";
        } else {
            marketFilingTime = "'" + info.getMarketFilingTime() + "'";
        }
        if (info.getCustomerPhone2() == null || info.getCustomerPhone2() == "") {
            customerPhone2 = "null";
        } else {
            customerPhone2 = "'" + info.getCustomerPhone2() + "'";
        }
        if (info.getPhoneSharShip() == null || info.getPhoneSharShip() == "") {
            phoneSharShip = "'本人'";
        } else {
            phoneSharShip = "'" + info.getPhoneSharShip() + "'";
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        info.setArrivalStore(ShiroKit.getUser().getOutpatientName());
        String selectSql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where reservations_id='" + info.getReservationsId() + "' limit 0,1";
        CustomerInfo info1 = sqlMapper.selectOne(selectSql, CustomerInfo.class);
        Double actualAccountBalance = 0.00;
        Double giveAccountBalance = 0.00;
        Double accountArrears = 0.00;
        if (info1 != null) {
            actualAccountBalance = info1.getActualAccountBalance();
            giveAccountBalance = info1.getGiveAccountBalance();
            accountArrears = info1.getAccountArrears();
        }
        if (info.getArchiving() == "" || info.getArchiving() == null) {
            info.setArchiving(ShiroKit.getUser().getAccount());
        }
        if (info.getScarchiving() == "" || info.getScarchiving() == null) {
            info.setScarchiving("");
        }
        info.setStatus("到店");
        info.setDiagnosisStatus("未看诊");
        info.setDealStatus("未开单");
        info.setStartTime(this.getStartTime());
        info.setEndTime(this.getEndTime());
        if (info.getDoctor() == "" || info.getDoctor() == null) {
            info.setDoctor("未指定医生");
        }
        if (info.getAddress() == "" || info.getAddress() == null) {
            info.setAddress("");
        }
        if (info.getAppointmentPayService() == "" || info.getAppointmentPayService() == null) {
            info.setAppointmentPayService("");
        }
        //设置黄页编号
        String yellowNumbers = "";
        if (StringUtil.isNotEmpty(info.getYellowNumber())) {  //填写了黄页编号
            yellowNumbers = info.getYellowNumber();
        } else {
            yellowNumbers = "0000123";
        }
        if ("null".equals(info.getOtherContact()) || info.getOtherContact() == null) {
            info.setOtherContact("");
        }
        String id = this.getId();
        String sql = "INSERT INTO `" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + "` (`id`,customer_phone2,`first_visit_date`,`actual_account_balance`,`give_account_balance`,`account_arrears`,`age_range`,`reservations_id`,`appointment_pay_service`,`arrival_store`,`phone_shar_ship`,`deal_status`, `customer_name`, `pseudonym`, `customer_phone`, " +
                "`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                "`field_tc`, `query_tc`, `customer_sex`, `customer_age`, `birthday`," +
                " `address`, `referee`, `referee_ship`, `id_card_no`," +
                " `fixed_telephone`, `is_have_children`, `email`, `zip_code`, " +
                "`customer_status`, `number_of_audits`, `auditor_time`, `final_auditor`," +
                "  `non_transaction_level`, `transaction_level`, `characters`, " +
                "`consumption_habits`, `is_return_visit`, `cause_of_no_return_visit`, `consum_ability`," +
                " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                " `province`, `cities`, `region`, `medical_record_number`, " +
                " `marital_status`, `occupation`, `archiving`, `entry_clerk`,`update_people`,`update_time`," +
                " `filing_time`, `arrival_time`, `remarks`, `status`, `diagnosis_status`,`arrival_status`,`doctor`,`start_time`,`end_time`," +
                "`create_appointment_time`,yellow_number,xcustomer_status,scarchiving,treatment_unit,szm,card_number," +
                "customer_sex_head,market_filing_time,isMarketTc,guide_doctor) VALUES" +
                "('" + id + "'," + customerPhone2 + ",'" + DateTimeKit.now() + "'," + actualAccountBalance + "," + giveAccountBalance + "," + accountArrears + ",'" + info.getAgeRange() + "','" + info.getReservationsId() + "','" + info.getAppointmentPayService() + "','" + info.getArrivalStore() + "'," + phoneSharShip + ",'" + info.getDealStatus() + "','" + info.getCustomerName().trim() + "','" + info.getPseudonym() +
                "','" + info.getCustomerPhone() + "','" + info.getOneMedium() + "','" + info.getTwoMedium() + "','" + info.getThrMedium() + "','" + info.getOneChannel() + "'" +
                ",'" + info.getTwoChannel() + "','" + info.getThrChannel() + "'" +
                ",'" + info.getFieldTc() + "','" + info.getFieldTc() + "'," + info.getCustomerSex() + "," + info.getCustomerAge() + "," + birthday +
                ",'" + info.getAddress() + "','" + info.getReferee() + "','" + info.getRefereeShip() + "','" + info.getIdCardNo() +
                "','" + info.getFixedTelephone() + "'," + info.getIsHaveChildren() + "," + "'" + info.getEmail() + "','" + info.getZipCode() +
                "','" + info.getCustomerStatus() + "'," + info.getNumberOfAudits() + "," + auditorTime + ",'" + info.getFinalAuditor() +
                "','" + info.getNonTransactionLevel() + "', '" + info.getTransactionLevel() + "','" + info.getCharacters() +
                "','" + info.getConsumptionHabits() + "'," + info.getIsReturnVisit() + ",'" + info.getCauseOfNoReturnVisit() + "','" + info.getConsumAbility() +
                "','" + info.getQqNumber() + "', '" + info.getWechatNumber() + "','" + info.getOtherContact() + "','" + info.getCountry() +
                "','" + info.getProvince() + "' ,'" + info.getCities() + "','" + info.getRegion() + "','" + info.getMedicalRecordNumber() + "','" + info.getMaritalStatus() + "'," +
                "'" + info.getOccupation() + "','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() +
                "','" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "'," + filingTime + ",'" + DateTimeKit.now() + "'" +
                ",'" + info.getRemarks() + "','" + info.getStatus() + "','" + info.getDiagnosisStatus() + "','2','" + info.getDoctor() + "','"
                + info.getStartTime() + "','" + info.getEndTime() + "','" + DateTimeKit.now() + "','" + yellowNumbers + "','"
                + info.getXcustomerStatus() + "','" + info.getScarchiving() + "','"
                + info.getTreatmentUnit() + "',firstconcats('" + info.getCustomerName() + "'),'" + info.getCardNumber() + "','"
                + customerPublicService.getSexPt(info)+"'," + marketFilingTime + ",1,'"+info.getGuideDoctor()+"');";
        String upda = "update reservations_info set customer_sex='" + info.getCustomerSex() + "'," +
                "customer_age=" + info.getCustomerAge() + ",customer_sex_head='"+customerPublicService.getSexPt(info)+"'," +
                "province='" + info.getProvince() + "',cities='" + info.getCities() + "'," +
                "region='" + info.getRegion() + "',address='" + info.getAddress() + "',occupation='" + info.getOccupation() + "'," +
                "identification_number='" + info.getMedicalRecordNumber() + "' where id='" + info.getReservationsId() + "'";
        sqlMapper.update(upda);
        String updasc = "update reservations_infosc set customer_sex='" + info.getCustomerSex() + "'," +
                "customer_age=" + info.getCustomerAge() + ",customer_sex_head='"+customerPublicService.getSexPt(info)+"'," +
                "province='" + info.getProvince() + "',cities='" + info.getCities() + "'," +
                "region='" + info.getRegion() + "',address='" + info.getAddress() + "',occupation='" + info.getOccupation() + "'," +
                "identification_number='" + info.getMedicalRecordNumber() + "' where id='" + info.getReservationsId() + "'";
        sqlMapper.update(updasc);
        //生成病历日志
        String sq11 = "INSERT INTO `customer_medical_number_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + ShiroKit.getUser().getAccount() + "','门店生成','" + ShiroKit.getUser().getId() + "','" + info.getCustomerStatus() + "', '"
                + info.getCustomerName() + "', '" + info.getCustomerPhone() + "','成功','" + ShiroKit.getUser().getOutpatientName() +
                "','生成的病历号为:" + info.getMedicalRecordNumber() + "','" + id + "','" + info.getReservationsId() + "');";
        sqlMapper.insert(sq11);
        //插入视图改动日志 新诊挂号
        String sqli = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + ShiroKit.getUser().getAccount() + "','初诊到店','" + ShiroKit.getUser().getId() + "','" + info.getCustomerStatus() + "', '"
                + info.getCustomerName() + "', '" + info.getCustomerPhone() + "','成功','" + ShiroKit.getUser().getOutpatientName() +
                "','初诊到店：预约->挂号','" + id + "','" + info.getReservationsId() + "');";
        sqlMapper.insert(sqli);   //门诊预约初诊到店挂号
        return sqlMapper.insert(sql);
    }

    //新诊到店
    public int insertxz(CustomerInfo info) {
        String birthday = "";
        String auditorTime = "";
        String phoneSharShip = "";
        String marketFilingTime = "";
        if (info.getBirthday() == null || info.getBirthday() == "") {
            birthday = "null";
        } else {
            birthday = "'" + info.getBirthday() + "'";
        }
        if (info.getAuditorTime() == null || info.getAuditorTime() == "") {
            auditorTime = "null";
        } else {
            auditorTime = "'" + info.getAuditorTime() + "'";
        }
        if (info.getMarketFilingTime() == null || info.getMarketFilingTime() == "") {
            marketFilingTime = "null";
        } else {
            marketFilingTime = "'" + info.getMarketFilingTime() + "'";
        }
        if (info.getPhoneSharShip() == null || info.getPhoneSharShip() == "") {
            phoneSharShip = "'本人'";
        } else {
            phoneSharShip = "'" + info.getPhoneSharShip() + "'";
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        info.setArrivalStore(ShiroKit.getUser().getOutpatientName());
        Double actualAccountBalance = 0.00;
        Double giveAccountBalance = 0.00;
        Double accountArrears = 0.00;
        if (info.getAddress() == "" || info.getAddress() == null) {
            info.setAddress("");
        }
        if (info.getAppointmentPayService() == "" || info.getAppointmentPayService() == null) {
            info.setAppointmentPayService("");
        }
        if ("null".equals(info.getOtherContact()) || info.getOtherContact() == null) {
            info.setOtherContact("");
        }
        String sql1 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where id='" + info.getId() + "'";
        CustomerInfo ccinfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        String remarksReservation = "";
        if (ccinfo.getRemarksReservation() == null || ccinfo.getRemarksReservation() == "") {
            remarksReservation = "null";
        } else {
            remarksReservation = "'" + ccinfo.getRemarksReservation() + "'";
        }
        String sql = "INSERT INTO `" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + "` (`id`,`first_visit_date`,`actual_account_balance`,`give_account_balance`,`account_arrears`," +
                "`age_range`,`reservations_id`,`appointment_pay_service`,`arrival_store`,`phone_shar_ship`," +
                "`deal_status`, `customer_name`, `pseudonym`, " +
                "`customer_phone`,`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                "`field_tc`, `query_tc`, `customer_sex`, `customer_age`, `birthday`,`address`, `referee`, `referee_ship`, `id_card_no`," +
                " `fixed_telephone`, `is_have_children`, `email`, `zip_code`,`customer_status`, `number_of_audits`, `auditor_time`, `final_auditor`," +
                "  `non_transaction_level`, `transaction_level`, `characters`, " +
                "`consumption_habits`, `is_return_visit`, `cause_of_no_return_visit`, `consum_ability`," +
                " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                " `province`, `cities`, `region`, `medical_record_number`, " +
                " `marital_status`, `occupation`, `archiving`, `entry_clerk`,`update_people`,`update_time`," +
                " `filing_time`, `arrival_time`, `remarks`,remarks_reservation, `status`, `diagnosis_status`,`arrival_status`,`doctor`,`start_time`,`end_time`," +
                "`create_appointment_time`,yellow_number,scarchiving,treatment_unit,szm,customer_sex_head,market_filing_time,isMarketTc,guide_doctor) VALUES" +
                "('" + info.getCustomerId() + "','" + DateTimeKit.now() + "'," + actualAccountBalance + "," + giveAccountBalance + "," + accountArrears +
                ",'" + info.getAgeRange() + "','" + info.getReservationsId() + "','" + info.getAppointmentPayService() + "','" + info.getArrivalStore() + "'," + phoneSharShip
                + ",'" + ccinfo.getDealStatus() + "','" + info.getCustomerName().trim() + "','" + info.getPseudonym() +
                "','" + info.getCustomerPhone() + "','" + info.getOneMedium() + "','" + info.getTwoMedium() + "','" + info.getThrMedium() + "','" + info.getOneChannel() + "'" +
                ",'" + info.getTwoChannel() + "','" + info.getThrChannel() + "'" +
                ",'" + info.getFieldTc() + "','" + info.getFieldTc() + "'," + info.getCustomerSex() + "," + info.getCustomerAge() + "," + birthday +
                ",'" + info.getAddress() + "','" + info.getReferee() + "','" + info.getRefereeShip() + "','" + info.getIdCardNo() +
                "','" + info.getFixedTelephone() + "'," + info.getIsHaveChildren() + "," + "'" + info.getEmail() + "','" + info.getZipCode() +
                "','" + info.getCustomerStatus() + "'," + info.getNumberOfAudits() + "," + auditorTime + ",'" + info.getFinalAuditor() +
                "','" + info.getNonTransactionLevel() + "', '" + info.getTransactionLevel() + "','" + info.getCharacters() +
                "','" + info.getConsumptionHabits() + "'," + info.getIsReturnVisit() + ",'" + info.getCauseOfNoReturnVisit() + "','" + info.getConsumAbility() +
                "','" + info.getQqNumber() + "', '" + info.getWechatNumber() + "','" + info.getOtherContact() + "','" + info.getCountry() +
                "','" + info.getProvince() + "' ,'" + info.getCities() + "','" + info.getRegion() + "','" + info.getMedicalRecordNumber() + "','" + info.getMaritalStatus() + "'," +
                "'" + info.getOccupation() + "','" + ccinfo.getArchiving() + "','" + ShiroKit.getUser().getAccount() +
                "','" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "','" + ccinfo.getFilingTime() + "','" + DateTimeKit.now() + "'" +
                ",'" + info.getRemarks() + "'," + remarksReservation + ",'到店','" + ccinfo.getDiagnosisStatus() + "','2','" + ccinfo.getDoctor() + "','"
                + ccinfo.getStartTime() + "','" + ccinfo.getEndTime() + "','" + ccinfo.getCreateAppointmentTime() + "','" + info.getYellowNumber() + "','"
                + info.getScarchiving() + "','"
                + info.getTreatmentUnit() + "',firstconcats('" + info.getCustomerName() + "'),'"
                + customerPublicService.getSexPt(info)+"'," + marketFilingTime + ",1,'"+info.getGuideDoctor()+"');";
        String upda = "update reservations_info set customer_name='" + info.getCustomerName() + "',customer_sex='" + info.getCustomerSex() + "'," +
                "customer_age=" + info.getCustomerAge() + ",customer_sex_head='"+customerPublicService.getSexPt(info)+"'," +
                "province='" + info.getProvince() + "',cities='" + info.getCities() + "'," +
                "region='" + info.getRegion() + "',address='" + info.getAddress() + "',occupation='" + info.getOccupation() + "'," +
                "identification_number='" + info.getMedicalRecordNumber() + "'," +
                "status='到店',first_visit_date='" + DateTimeKit.now() + "',arrival_store='" + ShiroKit.getUser().getOutpatientName() + "' " +
                " where id='" + info.getReservationsId() + "'";
        sqlMapper.update(upda);
//        String upca = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " set customer_name ='" + info.getCustomerName() + "'" +
//                ",birthday='" + info.getBirthday() + "'" +
//                ",age_range= " +
//                "(case " +
//                " WHEN (0<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<10) then '0-9' " +
//                " WHEN (10<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<20) then '10-19' " +
//                " WHEN (20<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<30) then '20-29' " +
//                " WHEN (30<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<40) then '30-39' " +
//                " WHEN (40<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<50) then '40-49' " +
//                " WHEN (50<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<60) then '50-59' " +
//                " WHEN (60<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<70) then '60-69' " +
//                " WHEN (70<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<80) then '70-79' " +
//                " WHEN (80<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<90) then '80-89' " +
//                " WHEN (90<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<100) then '90-99' " +
//                " else '99+' end)" +
//                ",customer_age=(DATE_FORMAT(FROM_DAYS(TO_DAYS(start_time)-TO_DAYS('" + info.getBirthday() + "')),'%Y')),customer_sex='" + info.getCustomerSex() + "'" +
//                ",medical_record_number='" + info.getMedicalRecordNumber() + "',referee='" + info.getReferee() + "',referee_ship='" + info.getRefereeShip() + "'" +
//                ",szm=firstconcats('" + info.getCustomerName() + "'),yellow_number='" + info.getYellowNumber() + "',customer_name='" + info.getCustomerName() + "',province='" + info.getProvince() + "',cities='" + info.getCities() + "',region='" + info.getRegion() + "',address='" + info.getAddress() + "'" +
//                ",occupation='" + info.getOccupation() + "',consum_ability='" + info.getConsumAbility() + "',consumption_habits='" + info.getConsumptionHabits() +
//                "',id_card_no='" + info.getIdCardNo() + "',fixed_telephone='" + info.getFixedTelephone() + "',qq_number='" + info.getQqNumber() +
//                "',wechat_Number='" + info.getWechatNumber() + "',email='" + info.getEmail() + "',zip_code='" + info.getZipCode() +
//                "',marital_status='" + info.getMaritalStatus() + "',is_have_children='" + info.getIsHaveChildren() + "',characters='" + info.getCharacters() +
//                "' where reservations_id ='" + info.getReservationsId() + "';";
//        sqlMapper.update(upca);
        String upda2 = "update customer_reservations_update_log set customer_id='" + info.getCustomerId() + "'" +
                " where customer_id='" + ccinfo.getId() + "'";
        sqlMapper.update(upda2);
        sqlMapper.insert(sql);
        String desql = "delete from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where id='" + ccinfo.getId() + "'";
        return sqlMapper.delete(desql);
    }

    //市场使用E看牙病历号点亮
    public int insertsc(ReservationsInfo info) {
        String filingTime = "";
        String phoneSharShip = "";
        if (info.getFilingTime() == null || info.getFilingTime() == "") {
            filingTime = DateTimeKit.now();
        } else {
            filingTime = "'" + info.getFilingTime() + "'";
        }
        if (info.getPhoneSharShip() == null || info.getPhoneSharShip() == "") {
            phoneSharShip = "'本人'";
        } else {
            phoneSharShip = "'" + info.getPhoneSharShip() + "'";
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        if (info.getArchiving() == "" || info.getArchiving() == null) {
            info.setArchiving(ShiroKit.getUser().getAccount());
        }
        info.setStatus("到店");
        info.setStartTime(this.getStartTime());
        info.setEndTime(this.getEndTime());
        if (info.getDoctor() == "" || info.getDoctor() == null) {
            info.setDoctor("未指定医生");
        }
        if (info.getAddress() == "" || info.getAddress() == null) {
            info.setAddress("");
        }
        if ("null".equals(info.getOtherContact()) || info.getOtherContact() == null) {
            info.setOtherContact("");
        }
        String cid = this.getId();
        String abbreviation = info.getAbbreviation() + "medicalrecord_info";
        String medicalRecordNumbe = this.getMedicalRecordNumbers(info.getCustomerPhone(), abbreviation);
        String sql = "INSERT INTO " + info.getAbbreviation() + "customer_info (`id`,`first_visit_date`,`age_range`,`reservations_id`,`appointment_pay_service`," +
                "`arrival_store`,`phone_shar_ship`,`deal_status`, `customer_name`, `customer_phone`, " +
                "`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                "`customer_sex`, `customer_age`," +
                " `address`,  `id_card_no`, `is_have_children`, " +
                "`customer_status`, " +
                " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                " `province`, `cities`, `region`, `medical_record_number`, " +
                " `archiving`, `entry_clerk`,`update_people`,`update_time`," +
                " `filing_time`, `arrival_time`, `remarks`, `status`, `diagnosis_status`,`arrival_status`,`doctor`,`start_time`,`end_time`," +
                "`create_appointment_time`,yellow_number,scarchiving,treatment_unit,szm) VALUES" +
                "('" + cid + "','" + DateTimeKit.now() + "','" + info.getAgeRange() + "','" + info.getReservationsId() + "','" + info.getAppointmentPayService() + "','"
                + info.getArrivalStore() + "'," + phoneSharShip + ",'未开单','" + info.getCustomerName().trim() +
                "','" + info.getCustomerPhone() + "','" + info.getOneMedium() + "','" + info.getTwoMedium() + "','" + info.getThrMedium() + "','" + info.getOneChannel() + "'" +
                ",'" + info.getTwoChannel() + "','" + info.getThrChannel() + "'" +
                "," + info.getCustomerSex() + "," + info.getCustomerAge() +
                ",'" + info.getAddress() + "','" + info.getIdCardNo() + "'," + info.getIsHaveChildren() +
                ",'新客首次','" + info.getQqNumber() + "', '" + info.getWechatNumber() + "','" + info.getOtherContact() + "','" + info.getCountry() +
                "','" + info.getProvince() + "' ,'" + info.getCities() + "','" + info.getRegion() + "','" + medicalRecordNumbe + "','"
                + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() +
                "','" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "'," + filingTime + ",'" + DateTimeKit.now() + "'" +
                ",'" + info.getRemarks() + "','" + info.getStatus() + "','未看诊','2','" + info.getDoctor() + "','"
                + info.getStartTime() + "','" + info.getEndTime() + "','" + DateTimeKit.now() + "','999','" + info.getArchiving() + "','"
                + info.getAppointmentPayService() + "',firstconcats('" + info.getCustomerName() + "'));";
        String upda = "update reservations_info set customer_name='" + info.getCustomerName() + "',customer_sex='" + info.getCustomerSex() + "'," +
                "customer_age=" + info.getCustomerAge() + ",province='" + info.getProvince() + "',cities='" + info.getCities() + "'," +
                "region='" + info.getRegion() + "',address='" + info.getAddress() + "',occupation='" + info.getOccupation() + "'," +
                "status='到店',outpatient_name='" + info.getArrivalStore() + "',arrival_store='" + info.getArrivalStore() + "'," +
                "first_visit_date='" + DateTimeKit.now() + "',ekyNumber='" + info.getEkyNumber() + "'," +
                "identification_number='" + medicalRecordNumbe + "' where id='" + info.getReservationsId() + "'";
        sqlMapper.update(upda);
        String sqlsc = "update reservations_infosc set status='到店',outpatient_name='" + info.getArrivalStore() + "'," +
                " first_visit_date='" + DateTimeKit.now() + "',arrival_store='" + info.getArrivalStore() + "',ekyNumber='" + info.getEkyNumber() + "'," +
                "identification_number='" + medicalRecordNumbe + "' where id = '" + info.getReservationsId() + "'";
        sqlMapper.update(sqlsc);
        //加入客户状态表
        String sqldd = "insert into distinction_customer_status (reservations_id,customer_name,customer_phone,arrival_store,abbreviation,deal_status,add_time,update_time,medical_record_number) " +
                " values('" + info.getReservationsId() + "','" + info.getCustomerName() + "','" + info.getCustomerPhone()
                + "','" + info.getArrivalStore() + "','" + info.getAbbreviation() + "','未成交','" + DateTimeKit.now() + "','" + DateTimeKit.now() + "','" + info.getMedicalRecordNumber() + "') ";
        sqlMapper.insert(sqldd);
        //插入视图改动日志 市场点亮
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + ShiroKit.getUser().getAccount() + "','市场人员点亮','" + ShiroKit.getUser().getId() + "','新客首次', '"
                + info.getCustomerName() + "', '" + info.getCustomerPhone() + "','成功','" + ShiroKit.getUser().getOutpatientName() +
                "','预约项目：" + info.getAppointmentPayService() + " </br>\n预约患者：" + info.getCustomerName() + "</br>\n预约时间：" + info.getStartTime() + " - " + info.getEndTime() + " " +
                "</br>\n预约状态：到店 </br>\n预约科室：" + info.getAppointmentPayService() + " </br>\n预约医生："
                + info.getDoctor() + " </br>\n咨询师： </br>\n备注： '," +
                "'" + cid + "','" + info.getReservationsId() + "');";
        sqlMapper.insert(sq11);
        //生成病历日志
        String insertSql = "INSERT INTO `customer_medical_number_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + ShiroKit.getUser().getAccount() + "','市场生成','" + ShiroKit.getUser().getId() + "','" + info.getCustomerStatus() + "', '"
                + info.getCustomerName() + "', '" + info.getCustomerPhone() + "','成功','" + ShiroKit.getUser().getOutpatientName() +
                "','生成的病历号为:" + medicalRecordNumbe + "','" + info.getId() + "','" + info.getReservationsId() + "');";
        sqlMapper.insert(insertSql);
        return sqlMapper.insert(sql);
    }

    public String insert2(CustomerInfo info) {
        String birthday = "";
        String phoneSharShip = "";
        if (info.getBirthday() == null || info.getBirthday() == "") {
            birthday = "null";
        } else {
            birthday = "'" + info.getBirthday() + "'";
        }
        if (info.getPhoneSharShip() == null || info.getPhoneSharShip() == "") {
            phoneSharShip = "'本人'";
        } else {
            phoneSharShip = "'" + info.getPhoneSharShip() + "'";
        }
        //查询已有手机号的预约集合
        List<ReservationsInfo> existList = reservationsService.getCustomerPhoneCounts(info.getCustomerPhone());
        int count = existList.size();
        if (!"'本人'".equals(phoneSharShip)) {
            info.setCustomerPhone(info.getCustomerPhone() + "+" + count);
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        info.setArrivalStore(ShiroKit.getUser().getOutpatientName());
        String selectSql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where reservations_id='" + info.getReservationsId() + "' limit 0,1";
        CustomerInfo info1 = sqlMapper.selectOne(selectSql, CustomerInfo.class);
        Double actualAccountBalance = 0.00;
        Double giveAccountBalance = 0.00;
        Double accountArrears = 0.00;
        if (info1 != null) {
            actualAccountBalance = info1.getActualAccountBalance();
            giveAccountBalance = info1.getGiveAccountBalance();
            accountArrears = info1.getAccountArrears();
        }
        if (info.getArchiving() == "" || info.getArchiving() == null) {
            info.setArchiving(ShiroKit.getUser().getAccount());
        }
        info.setStatus("到店");
        info.setDiagnosisStatus("未看诊");
        info.setDealStatus("未开单");
        String firstVisitDate = "";
        if (info.getFirstVisitDate() != null && info.getFirstVisitDate() != "") {
            firstVisitDate = "'" + info.getFirstVisitDate() + "'";
        } else {
            firstVisitDate = "null";
        }
        if (info.getAddress() == "" || info.getAddress() == null) {
            info.setAddress("");
        }
        info.setArrivalStore(ShiroKit.getUser().getOutpatientName());
        String sql = "INSERT INTO `" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + "` (`id`,`diagnosis_status`,`first_visit_date`," +
                "`actual_account_balance`,`give_account_balance`,`account_arrears`,`age_range`,`reservations_id`," +
                "`arrival_store`,`phone_shar_ship`,`deal_status`, `customer_name`, " +
                "`customer_phone`, " +
                "`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                " `customer_sex`, `customer_age`, `birthday`," +
                " `address`," + "`customer_status`, " + " `is_return_visit`," +
                " `province`, `cities`, `region`, `medical_record_number`, " +
                " `archiving`, `entry_clerk` ,`update_people`,`filing_time`, xcustomer_status,treatment_unit,szm,customer_sex_head) VALUES" +
                "('" + this.getId() + "','未看诊'," + firstVisitDate + "," + actualAccountBalance + "," + giveAccountBalance + "," + accountArrears + ",'"
                + info.getAgeRange() + "','" + info.getReservationsId() + "','"
                + info.getArrivalStore() + "'," + phoneSharShip + ",'" + info.getDealStatus() + "','"
                + info.getCustomerName().trim() +
                "','" + info.getCustomerPhone() + "','" + info.getOneMedium() + "','" + info.getTwoMedium() +
                "','" + info.getThrMedium() + "','" + info.getOneChannel() +
                "','" + info.getTwoChannel() + "','" + info.getThrChannel() +
                "','" + info.getCustomerSex() + "'," + info.getCustomerAge() + "," + birthday +
                ",'" + info.getAddress() + "','预约'," + info.getIsReturnVisit() +
                ",'" + info.getProvince() + "' ,'" + info.getCities() + "','" + info.getRegion() + "','" +
                this.getMedicalRecordNumbers(info.getCustomerPhone(), redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"medicalrecord_info",0)+"") + "','" +
                info.getArchiving() + "','" + ShiroKit.getUser().getAccount() +
                "','" + ShiroKit.getUser().getAccount() + "' ,'" + info.getFilingTime() + "' ,'" + info.getXcustomerStatus()
                + "','" + info.getTreatmentUnit() + "',firstconcats('" + info.getCustomerName() + "'),'"
                + customerPublicService.getSexPt(info)+"');";
        return sqlMapper.insert(sql) + "," + this.getId();
    }

    public String geturl(CustomerInfo customerInfo) {
        String ticket = "";
        String access_token = getAccessToken();
        String sceneStr = customerInfo.getCustomerName() + customerInfo.getCustomerPhone();
        com.alibaba.fastjson.JSONObject jsonObject = wxService.qrCodeCreateTmpTicket(access_token, sceneStr);
        if (null != jsonObject) {
            ticket = jsonObject.getString("ticket");
        }
        if (!"".equals(ticket)) {
            String sesql = "select reservations_id from members_user where reservations_id='" + customerInfo.getReservationsId() + "' limit 1";
            MembersUser membersUser = sqlMapper.selectOne(sesql, MembersUser.class);
            if (membersUser == null) {
                String insql = "insert into members_user (reservations_id,ticket,account,name,password,phone,medical_record_number,address,abbreviation,sex,age,birthday) " +
                        " select reservations_id,'" + jsonObject.getString("ticket") + "',customer_phone,customer_name,123,customer_phone,medical_record_number,address,'" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"" + "',customer_sex,customer_age,birthday " +
                        " from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where id='" + customerInfo.getId() + "';";
                sqlMapper.insert(insql);
            } else {
                String upsql = "update members_user a set a.ticket='" + jsonObject.getString("ticket") + "'where reservations_id='" + customerInfo.getReservationsId() + "'";
                sqlMapper.update(upsql);
            }
        }
        return wxService.qrCodePictureUrl(ticket);
    }

    public String getAccessToken() {
        String accessToken = "";
        String getTokenSql = "select access_token as accessToken from wx_parameter_info where app_id='" + redisTemplate.opsForList().index("APPID",0) + "' limit 1";
        WxTokenInfo wxTokenInfo = sqlMapper.selectOne(getTokenSql, WxTokenInfo.class);
        if (wxTokenInfo == null) {
            accessToken = WebChatConfig.getToken(redisTemplate.opsForList().index("APPID",0)+"",redisTemplate.opsForList().index("AppSecret",0)+"").getAccessToken();
            String updTokenSql = "update wx_parameter_info set access_token='" + accessToken + "' where app_id='" + redisTemplate.opsForList().index("APPID",0)+"" + "';";
            sqlMapper.update(updTokenSql);
        } else {
            if ("".equals(wxTokenInfo.getAccessToken()) || "null".equals(wxTokenInfo.getAccessToken())) {
                accessToken = WebChatConfig.getToken(redisTemplate.opsForList().index("APPID",0)+"",redisTemplate.opsForList().index("AppSecret",0)+"").getAccessToken();
                String updTokenSql = "update wx_parameter_info set access_token='" + accessToken + "' where app_id='" + redisTemplate.opsForList().index("APPID",0)+"" + "';";
                sqlMapper.update(updTokenSql);
            }
            accessToken = wxTokenInfo.getAccessToken();
        }
        return accessToken;
    }

    public int toVoid(CustomerInfo customerInfo) {
        String sqlcu = "select reservations_id from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where id = '" + customerInfo.getId() + "' limit 1";
        CustomerInfo customerInfo1 = sqlMapper.selectOne(sqlcu, CustomerInfo.class);
        String sqlse = "select id from reservations_infosc t where id='" + customerInfo1.getReservationsId() + "' " +
                " and left(t.first_visit_date,10)=left(now(),10) and t.arrival_store='" + ShiroKit.getUser().getOutpatientName() + "'";
        CustomerInfo customerInfo2 = sqlMapper.selectOne(sqlse, CustomerInfo.class);
        if (customerInfo2 != null) { //匹配市场初到，将市场到店状态改回预约
            String sqlsc = "update reservations_infosc set status='预约' , first_visit_date=null ,arrival_store=null where id = '" + customerInfo2.getId() + "'";
            sqlMapper.update(sqlsc);
            String sql = "update reservations_info set status='预约' , first_visit_date=null ,arrival_store=null  where id = '" + customerInfo2.getId() + "'";
            sqlMapper.update(sql);
        } else {
            String sqlz = "select id from reservations_info t where id='" + customerInfo1.getReservationsId() + "' " +
                    " and left(t.first_visit_date,10)=left(now(),10) and t.arrival_store='" + ShiroKit.getUser().getOutpatientName() + "'";
            CustomerInfo customerInfoz = sqlMapper.selectOne(sqlz, CustomerInfo.class);
            if (customerInfoz != null) {
                String deletesql = "delete from reservations_info where id='" + customerInfo1.getReservationsId() + "'";
                sqlMapper.delete(deletesql);
            }
        }
        String sql = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " set customer_status='作废' , to_void_remarks='" + customerInfo.getToVoidremarks() + "'" +
                ", to_void_people='" + ShiroKit.getUser().getAccount() + "',to_void_time='" + DateTimeKit.now() + "',arrival_status='8' " +
                " where id = '" + customerInfo.getId() + "'";
        return sqlMapper.update(sql);
    }

    public int update(CustomerInfo info) {
        String birthday = "";
        String auditorTime = "";
        if (info.getBirthday() == null || info.getBirthday() == "") {
            birthday = "null";
        } else {
            birthday = "'" + info.getBirthday() + "'";
        }
        if (info.getAuditorTime() == null || info.getAuditorTime() == "") {
            auditorTime = "null";
        } else {
            auditorTime = "'" + info.getAuditorTime() + "'";
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        if (info.getAddress() == "" || info.getAddress() == null) {
            info.setAddress("");
        }
        String sql = "UPDATE " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + "  SET customer_name='" + info.getCustomerName().trim() + "', customer_phone='"
                + info.getCustomerPhone() + "',  field_tc='" + info.getFieldTc() + "'," +
                " `age_range`='" + info.getAgeRange() + "', customer_sex='" + info.getCustomerSex() + "',`phone_shar_ship`='" + info.getPhoneSharShip()
                + "',  customer_age=" + info.getCustomerAge() + ",  birthday=" + birthday + "," +
                " is_have_children=" + info.getIsHaveChildren() + ", is_return_visit=" + info.getIsReturnVisit() + ", customer_status='"
                + info.getCustomerStatus() + "',medical_record_number='" + info.getMedicalRecordNumber() + "', number_of_audits="
                + info.getNumberOfAudits() + ", auditor_time=" + auditorTime + ",  consum_ability='" + info.getConsumAbility() + "', " +
                " qq_number='" + info.getQqNumber() + "',  wechat_number='" + info.getWechatNumber() + "', " +
                " one_medium='" + info.getOneMedium() + "',two_medium='" + info.getTwoMedium() + "',thr_medium='" + info.getThrMedium() + "'," +
                " one_channel='" + info.getOneChannel() + "',two_channel='" + info.getTwoChannel() + "',thr_channel='" + info.getThrChannel() + "'," +
                " address='" + info.getAddress() + "',  marital_status=" + info.getMaritalStatus() + ",  occupation='" + info.getOccupation() + "' , " +
                " reservations_id='" + info.getReservationsId() + "', appointment_pay_service='" + info.getAppointmentPayService()
                + "', update_people='" + ShiroKit.getUser().getAccount() + "',update_time='" + DateTimeKit.now()
                + "', remarks='" + info.getRemarks() +
                "' WHERE id='" + info.getId() + "' ";
        return sqlMapper.update(sql);
    }

    //点亮获取病历号
    public String getMedicalRecordNumbers(String customerPhone, String abbreviation) {
        String upsql = "update " + abbreviation + " set customer_phone=null where customer_phone='" + customerPhone + "' ";
        String usql = "update " + abbreviation + " set customer_phone='" + customerPhone + "'" +
                " where customer_phone is null order by id asc limit 1 ";
        sqlMapper.update(upsql);
        sqlMapper.update(usql);
        String ssql2 = "select concat(prefix_part_one,prefix_part_two,num) as medicalRecordNumber from " + abbreviation + " where customer_phone='" + customerPhone + "' limit 1";
        CustomerInfo cinfo2 = sqlMapper.selectOne(ssql2, CustomerInfo.class);
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("tableName", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0));
        params.put("medicalRecordNumber", cinfo2.getMedicalRecordNumber());
        return cinfo2.getMedicalRecordNumber();
    }

}